package com.mycompany.oj.Mapper.Impl;

import com.mycompany.oj.Mapper.TeacherMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 *
 * @author asus
 */
@Component
public class MyTeacherMapperImpl implements TeacherMapper {

    @Autowired
    SqlOperator sqloperator;

    //获取管理员图片
    @Override
    public List<Map<String, Object>> getTeacherPicureMapper(String username) {
        String stmt = "Select picture from Teacher where Account_id =  ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, username);
        return temp;
    }

    //获取管理员名字
    @Override
    public List<Map<String, Object>> getTeacherNameMapper(String username) {
        String stmt = "Select teacher_name from Teacher where Account_id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, username);
        return temp;
    }

    //获取每日提交的数目
    @Override
    public List<Map<String, Object>> getEverydayPostNumberMapper() {
        String stmt = "Select * from Everyday_post_number";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //获取学生做对了几道题
    @Override
    public List<Map<String, Object>> getStudentPostCourseNumber(String username) {
        String stmt = "Select number from Student_post_course_number where id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, username);
        return temp;
    }

    //获取学生做了几道题
    @Override
    public List<Map<String, Object>> getStudentPassCourseNumber(String username) {
        String stmt = "Select number from Student_pass_course_number where Account_id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, username);
        return temp;
    }

    //获取学生
    @Override
    public List<Map<String, Object>> getStudentInformation() {
        String stmt = "Select id,schoolid,student_name,Account_id from Student";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //获取注册用户的数量
    @Override
    public List<Map<String, Object>> getStudentCount() {
        String stmt = "SELECT count(*) number from Student;";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //获取竞赛题目数量
    @Override
    public List<Map<String, Object>> getQuestionNumber() {
        String stmt = "SELECT count(*) number from Question;";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //获取实验题目数量
    @Override
    public List<Map<String, Object>> getCourseNumber() {
        String stmt = "SELECT count(*) number from Question_course;";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //获取总体情况
    public List<Map<String, Object>> getTotalInformation() {
        String stmt = "Select * from course_count";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    @Override
    public List<Map<String, Object>> getAllInformation() {
        String stmt = "SELECT * from Section_number;";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    public List<Map<String, Object>> getAllInformationPlus(String course) {
        String stmt = "SELECT * from Section_number where coursename = ?;";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course);
        return temp;
    }

    @Override
    public List<Map<String, Object>> getAllInformationT() {
        String stmt = "SELECT * from Question_Course_total_number;";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //修改题库
    @Override
    public int changeQuestion(String question_name, String question_txt, String question_id) {
        String stmt = "Update Question set question_name = ?,question_text = ? where id=?";
        int temp = sqloperator.update(stmt, question_name, question_txt, question_id);
        return temp;
    }

    //修改题库
    @Override
    public int changeCourseQuestion(String question_name, String question_txt, String question_id) {
        String stmt = "Update Question_course set question_name = ?,question_text = ? where id=?";
        int temp = sqloperator.update(stmt, question_name, question_txt, question_id);
        return temp;
    }

    //获取用户通过实验数
    @Override
    public List<Map<String, Object>> passNumber(String course_name) {
        String stmt = "Select number from Total_Pass_number where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }

    //获取用户提交数
    @Override
    public List<Map<String, Object>> postNumber(String course_name) {
        String stmt = "Select number from Total_Post_number where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }

    //获取实验总数
    @Override
    public List<Map<String, Object>> sectionNumber(String course_name) {
        String stmt = "Select number,time from Question_Course_total_number where coursename = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }

    //获取实验总题数
    @Override
    public List<Map<String, Object>> sectionQuestionNumber(String course_name) {
        String stmt = "Select number from Question_Course_total_number where coursename = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }

    public List<Map<String, Object>> allQuestion(String course_name) {
        String stmt = "Select section_num,question_name,id from Section_question_all where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }

    //学生总人数
    @Override
    public List<Map<String, Object>> studentNumber() {
        String stmt = "Select count(*) number from Student";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //学生信息
    public List<Map<String, Object>> studentInformation() {
        String stmt = "Select * from Student";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    @Override
    public List<Map<String, Object>> everyStudentJindu(String course_name) {
        String stmt = "Select number,Account_id from everyStudentFinishedCourseNumber where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }

    public List<Map<String, Object>> allAccountId() {
        String stmt = "Select id from Account";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }
    
    @Override
    public List<Map<String, Object>> questionFinishedNumber(String course_name, String id, String Section_id) {
        String stmt = "Select number,id,Section_id from Every_question_finish_number where coursename = ? and id = ? and Section_id=?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name, id, Section_id);
        return temp;
    }

    //获取截至日期
    @Override
    public List<Map<String, Object>> getEndData(String coursename, String sectionnum) {
        String stmt = "Select section_end_time,section_gogal from getEndData where name = ? and section_num = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, coursename, sectionnum);
        return temp;
    }

    //获取各个实验的id
    @Override
    public List<Map<String, Object>> getCourseId(String coursename) {
        String stmt = "Select id from Every_course_id where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, coursename);
        return temp;
    }

    //获取各个实验的id
    public List<Map<String, Object>> getCourseSectionNumId(String coursename) {
        String stmt = "Select id from Every_course_section_num where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, coursename);
        return temp;
    }

    @Override
    public List<Map<String, Object>> getSectionNum(String coursename, String Section_id) {
        String stmt = "Select number from Question_Section_number where name = ? and section_num = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, coursename, Section_id);
        return temp;
    }

    public List<Map<String, Object>> getStudentName(String student_id) {
        String stmt = "Select student_name from Student where id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, student_id);
        return temp;
    }

    //各个章节id
    @Override
    public List<Map<String, Object>> getQuestionCourseId(String coursename, String Section_id) {
        String stmt = "Select id,name from Question_Section_id where name = ? and Section_num = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, coursename, Section_id);
        return temp;
    }

    public List<Map<String, Object>> ifPass(String question_course_id, String Student_id) {
        String stmt = "Select * from Question_course_register where Student_id = ? and Question_course_id = ? and question_result = 'True'";
        List<Map<String, Object>> temp = sqloperator.query(stmt, Student_id, question_course_id);
        return temp;
    }

    @Override
    public int deleteAnswer(String question_id) {
        String stmt = "Delete * from Question where id = ?";
        int temp = sqloperator.delete(stmt, question_id);
        return temp;
    }

    @Override
    public List<Map<String, Object>> everyStudentFinishedCourseNumber(String coursename, String Account_id) {
        String stmt = "Select number from everyStudentFinishedCourseNumber where name = ? and Account_id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, coursename, Account_id);
        return temp;
    }

    public List<Map<String, Object>> everyStudentDoCourseNumber(String coursename, String Account_id) {
        String stmt = "Select number from everyStudentDoCourseNumber where name = ? and Account_id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, coursename, Account_id);
        return temp;
    }

    public List<Map<String, Object>> getKindQuestionNumber() {
        String stmt = "Select * from questionNumber";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //上传蓝桥题目
    @Override
    public String[] importquestion(String kind_id, String standard_answer, String question_text, String Difficult_id, String question_name, String language) {
        String stmt = "INSERT INTO Question(question_name,Difficult_id,question_text,Kind_id) VALUES (?,?,?,?)";
        String[] temp = sqloperator.insert(stmt, question_name, Difficult_id, question_text, kind_id);
        int id = Integer.parseInt(temp[0]);
        String stmt2 = "INSERT INTO Student_answer(Question_id,Student_id,answer,language) VALUES (?,?,?,?)";
        String[] temp2 = sqloperator.insert(stmt2, id, 1, standard_answer, language);
        return temp2;
    }

    //上传实验题目
    @Override
    public String[] importcoursequestion(String Section_id, String question_name, String question_text, String question_answer) {
        String stmt = "INSERT INTO Question_course(question_name,Section_id,question_text,standard_answer) VALUES (?,?,?,?)";
        String[] temp = sqloperator.insert(stmt, question_name, Section_id, question_text, question_answer);
        return temp;
    }

    //获取课程
    public List<Map<String, Object>> getCourse() {
        String stmt = "Select name from Course";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    @Override
    public List<Map<String, Object>> getSectionid(String coursename, String setion_num) {
        String stmt = "Select id from get_Section_id where name = ? and section_num = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, coursename, setion_num);
        return temp;
    }

    //昨日提交量
    public List<Map<String, Object>> getYesterdayQuestionNum() {
        String stmt = "Select number from yeterday_question ";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //获取昨天的实验提交数
    public List<Map<String, Object>> getYesterdayCourseNum() {
        String stmt = "Select number from yeterday_course ";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //获取昨天的正确数
    public List<Map<String, Object>> getYesterdayQuestionTrueNum() {
        String stmt = "Select number from yeterday_true_question ";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //获取昨天的实验正确数
    public List<Map<String, Object>> getYesterdayCourseTrueNum() {
        String stmt = "Select number from yeterday_true_course ";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //
    public List<Map<String, Object>> everyCoursePostNumber(String course_id) {
        String stmt = "Select number from everyCoursePostNumber  where Question_course_id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_id);
        return temp;
    }

    //实验情况
    public List<Map<String, Object>> getCoursePosition(String course_name) {
        String stmt = "Select student_name ,schoolid,Section_num,Question_course_id from Student_pass_courss where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }

    //学号
    public List<Map<String, Object>> getSchoolid() {
        String stmt = "Select schoolid from Student";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //章节号
    public List<Map<String, Object>> getNewCourseId(String course_name) {
        String stmt = "Select id from Course_Section_num where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }



    public List<Map<String, Object>> getSectionNumber(String course) {
        String stmt = "Select * from Every_course_question_number where course_name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course);
        return temp;
    }

    //获取Courseid 获取sectionid 获取question_course_id 获取question_course_register_id
    public List<Map<String, Object>> getCourseIdByName(String course_name) {
        String stmt = "SELECT Course.id from Course where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, course_name);
        return temp;
    }

    public List<Map<String, Object>> getSectionId(String id) {
        String stmt = "SELECT Section.id from Section where Course_id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, id);
        return temp;
    }

    public List<Map<String, Object>> getQuestionCourseId(String id) {
        String stmt = "SELECT Question_course.id from Question_course where Section_id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, id);
        return temp;
    }

    public List<Map<String, Object>> getQuestionCourseRegisterId(String id) {
        String stmt = "SELECT Question_course_register.id from Question_course_register where Question_course_id = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, id);
        return temp;
    }

    //删除question_course_register_id
    public int delelteQuestionRegisterId(String id) {
        String stmt = "Delete * from Question_course_register where id = ?";
        int temp = sqloperator.delete(stmt, id);
        return temp;
    }

    //删除questiob_course_id
    public int delelteQuestionCourseId(String id) {
        String stmt = "Delete * from Question_course where id = ?";
        int temp = sqloperator.delete(stmt, id);
        return temp;
    }

    //删除section
    public int delelteSectionId(String id) {
        String stmt = "Delete * from Section where id = ?";
        int temp = sqloperator.delete(stmt, id);
        return temp;
    }

    //删除course
    public int delelteCourseId(String id) {
        String stmt = "Delete * from Course where id = ?";
        int temp = sqloperator.delete(stmt, id);
        return temp;
    }

    //年级的模糊查询
    public List<Map<String, Object>> doSearch(String id) {
        String stmt = "select id,schoolid,student_name,Account_id from Student where schoolid like '" + id + "%'";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }


    //姓名+年级的查询
    public List<Map<String, Object>> doSearchNameAndGrad(String name, String grade) {
        String stmt = "select id,schoolid,student_name,Account_id from Student where student_name =? and schoolid like'" + grade + "%'";
        List<Map<String, Object>> temp = sqloperator.query(stmt, name);
        return temp;
    }

    //姓名+年级的查询
    public List<Map<String, Object>> doSearchGradeandschoolid(String grade, String schoolid) {
        String stmt = "select id,schoolid,student_name,Account_id from Student where schoolid =? and schoolid like'" + grade + "%'";
        List<Map<String, Object>> temp = sqloperator.query(stmt, schoolid);
        return temp;
    }

    //姓名+学号
    public List<Map<String, Object>> doSearchNameAndSchoolid(String name, String schoolid) {
        String stmt = "select id,schoolid,student_name,Account_id from Student where student_name =? and schoolid = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt, name, schoolid);
        return temp;
    }

    //删除用户
    public void deleteStudentRegister(String student_id) {
        String stmt = "Delete * from Answer_register where Student_id = ?";
        String stmt2 = "Delete * from Question_course_register where Student_id";
        String stmt3 = "Delete * from Student where id = ?";
        int temp = sqloperator.delete(stmt, student_id);
        int temp2 = sqloperator.delete(stmt2, student_id);
        int temp3 = sqloperator.delete(stmt3, student_id);
    }

    public int doChangeSection(String course_name, String id, String name, String gogal, String end_data) {
        String stmt = "Update Section,Course set section_name = ?,section_gogal = ?,section_end_time = ? where section_num = ? and Course.id = Course_id and Course.name = ? ";
        int temp = sqloperator.update(stmt, name, gogal, end_data, id, course_name);
        return temp;
    }

    //停用问题
    public int doStopQuestion(String id) {
        String stmt = "Update Question_course set stop = 1 where id = ?";
        int temp = sqloperator.update(stmt, id);
        return temp;
    }

    //停用章节
    public int doStopSection(String coursename, String section_num) {
        String stmt = "Update Section set stop = 1 where Course_id = Course.id and Course.name = ? and section_num = ?";
        int temp = sqloperator.update(stmt, coursename, section_num);
        return temp;
    }

    //停用课程
    public int doStopCourse(String id) {
        String stmt = "Update Course set stop = 1 where Course.name = ?";
        int temp = sqloperator.update(stmt, id);
        return temp;
    }

    //启用问题
    public int doStartQuestion(String id) {
        String stmt = "Update Question_course set stop = 0 where id = ?";
        int temp = sqloperator.update(stmt, id);
        return temp;
    }

    //启用章节
    public int do_start_section(String coursename, String section_num) {
        String stmt = "Update Section set stop = 0 where Course_id = Course.id and Course.name = ? and section_num = ?";
        int temp = sqloperator.update(stmt, coursename, section_num);
        return temp;
    }

    //启用课程
    public int doStartCourse(String id) {
        String stmt = "Update Course set stop = 0 where Course.name = ?";
        int temp = sqloperator.update(stmt, id);
        return temp;
    }
    //获取停用课程

    public List<Map<String, Object>> doGetStopCourse() {
        String stmt = "Select id from Course where stop = 1";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }
    //获取停用章节

    public List<Map<String, Object>> doGetStopSection() {
        String stmt = "Select id from Section where stop = 1";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }
    //获取停用问题

    public List<Map<String, Object>> doGetStopQuestion() {
        String stmt = "Select id from Question_course where stop = 1";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        return temp;
    }

    //添加章节
    public String[] addSection(String course_name, String section_name, String section_num, String gogal, String end_data) {
        String stmt = "Select id from Course where name = ?";
        List<Map<String, Object>> temp = sqloperator.query(stmt);
        String stmt2 = "Insert into Section(Course_id,section_num,section_name,section_gogal,section_end_time,stop) values(?,?,?,?,?,0)";
        String[] temp2 = sqloperator.insert(stmt2, temp.get(0).get("id").toString(), section_num, section_name, gogal, end_data);
        return temp2;
    }

    //添加问题
    public String[] addQuestion(String coursename, String section_num, String question_name, String question_text, String question_data, String standard_answer) {
        String stmt2 = "Select Section.id from Section,Course where Course_id = Course.id and section_num = ? and Course.name = ?";
        List<Map<String, Object>> temp2 = sqloperator.query(stmt2, section_num, coursename);
        String stmt = "Insert into Question_course(Section_id,question_name,question_text,question_data,standard_answer,stop) values(?,?,?,?,?,0)";
        String[] temp = sqloperator.insert(stmt, temp2.get(0).get("id").toString(), question_name, question_text, question_data, standard_answer, 0);
        return temp;
    }
}
